package com.yr.ryjk.pay;

import java.io.OutputStream;
import java.util.List;

import com.yr.ryjk.pay.vo.CashOutDoctorVO;
import com.yr.ryjk.util.DateUtil;

import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class ExcelGenerator {
	public void genExcelStream(List<CashOutDoctorVO> list, OutputStream os)
			throws Exception {
		try {
			WritableWorkbook wwb = Workbook.createWorkbook(os);
			// 创建Excel工作表 指定名称和位置
			WritableSheet ws = wwb.createSheet("用户项目统计", 0);
			// 设置表格的列宽度
			ws.setColumnView(0, 10);
			ws.setColumnView(1, 15);
			ws.setColumnView(2, 15);
			ws.setColumnView(3, 25);
			ws.setColumnView(4, 15);
			ws.setColumnView(5, 30);
			ws.setColumnView(6, 25);
			ws.setColumnView(7, 30);

			// **************往工作表中添加数据*****************
			// 定义字体格式
			WritableFont titleFont = new WritableFont(
					WritableFont.createFont("微软雅黑"), 14, WritableFont.NO_BOLD);
			WritableFont thFont = new WritableFont(
					WritableFont.createFont("楷体 _GB2312"), 12,
					WritableFont.BOLD);
			WritableFont contentFont = new WritableFont(
					WritableFont.createFont("楷体 _GB2312"), 12,
					WritableFont.NO_BOLD);

			WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
			WritableCellFormat thFormat = new WritableCellFormat(thFont);
			WritableCellFormat contentFormat = new WritableCellFormat(
					contentFont);

			// 设置格式居中对齐
			titleFormat.setAlignment(jxl.format.Alignment.CENTRE);
			thFormat.setAlignment(jxl.format.Alignment.CENTRE);
			thFormat.setBackground(Colour.GRAY_25);
			contentFormat.setAlignment(jxl.format.Alignment.LEFT);

			thFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
			contentFormat.setBorder(Border.ALL, BorderLineStyle.THIN,
					Colour.BLACK);

			// ***************将定义好的单元格添加到工作表中*****************
			// ws.mergeCells(0, 0, 4, 1);// 合并单元格
			ws.addCell(new Label(0, 0, "序号", thFormat));
			ws.addCell(new Label(1, 0, "医生姓名", thFormat));
			ws.addCell(new Label(2, 0, "性别", thFormat));
			ws.addCell(new Label(3, 0, "手机号", thFormat));
			ws.addCell(new Label(4, 0, "提现金额(元)", thFormat));
			ws.addCell(new Label(5, 0, "开户行", thFormat));
			ws.addCell(new Label(6, 0, "银行卡号", thFormat));
			ws.addCell(new Label(7, 0, "申请时间", thFormat));

			// 向Excel中插入数据
			CashOutDoctorVO vo;
			int j = 0;
			for (int i = 1; i < list.size() + 1; i++) {
				j = i - 1;
				vo = list.get(j);

				ws.addCell(new Number(0, i, i, contentFormat));
				ws.addCell(new Label(1, i, vo.getRealName(), contentFormat));
				ws.addCell(new Label(2, i, vo.getGender() == 0 ? "女" : "男",
						contentFormat));
				ws.addCell(new Label(3, i, vo.getPhone(), contentFormat));
				ws.addCell(new Number(4, i, vo.getCoinAmount() / 10,
						contentFormat));
				ws.addCell(new Label(5, i, vo.getOpenBank(), contentFormat));
				ws.addCell(new Label(6, i, vo.getCardNo(), contentFormat));
				ws.addCell(new Label(7, i, DateUtil.getFullStrOfDate(vo.getOperateTime()), contentFormat));
			}
			// 写入工作表完毕，关闭流
			wwb.write();
			wwb.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}